<?php
/*
 * @Author: your name
 * @Date: 2020-07-27 15:07:44
 * @LastEditTime: 2020-12-24 11:28:16
 * @LastEditors: Please set LastEditors
 * @Description: In User Settings Edit
 * @FilePath: /special/application/adminapiv3/service/HostBulkImport.php
 */ 
namespace app\adminapiv3\service\asset_manage\host;

use app\adminapiv3\model\access_log\HostAccessLogModel;
use app\adminapiv3\model\learn_data\HostLearnDataModel;
use app\adminapiv3\service\BaseService;
use app\common\error_message\AuthError;
use app\common\exception\CustomException;
use app\common\service\asset_manage\host\Host;
use app\common\service\platform_manage\ServerAuthService;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\Db;
use think\Exception;
use think\facade\Validate;

class HostBulkImport extends BaseService
{
    /**
     * @name: 模板
     * @Param: 
     * @Description: 
     * @Author: foo
     * @Date: 2020-07-27 15:10:44
     */
    public function template ()
    {
        global $logArray;
        $logArray['content'] = '模板下载';

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet()->setTitle('主机表');
        $title = array_values($this->keyValue());
        $this->sheetData($sheet, $title, []);
        
        $writer = new Xlsx($spreadsheet);
        $writer->save('template.xlsx');
        
    }

    /**
     * @name: 导入
     * @Param: 
     * @Description: 
     * @Author: foo
     * @Date: 2020-07-27 15:10:57
     */
    public function import ($file)
    {

        global $logArray;
        $logArray['content'] = '批量导入资产';

        if(empty($file))
        {
            $this->setError($this->CODE_FAIL, '上传的文件为空');
            return false;
        }

        $folder = '../runtime/bulk_host_import/';

        // 移动到框架应用根目录/uploads/ 目录下
        $data = $file->validate(['size'=>1000000,'ext'=>'xlsx'])->move($folder);
        if(empty($data))
        {
            $this->setError($this->CODE_FAIL, $file->getError());
            return false;
        }

        $file_path = $folder.$data->getSaveName();

        if (!file_exists($file_path))
        {
            $this->setError(422, '文件不存在');
            return false;
        }

        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($file_path);
        
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow();//总行数
        $highestColumn = $worksheet->getHighestColumn();//总列数

        \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
        $lines = $highestRow - 1;//总条数

        if($lines <= 0)
        {
            $this->setError(422, '没有数据');
            return false;
        }

        if($highestColumn != 'B')
        {
            $this->setError(422, '文件内容不合规');
            return false;
        }

        $i = 0;
        $fail_file_path = 'bulk_host_import_log/'. date('Y/m/d') ;
        if(!file_exists($fail_file_path))
        {
            mkdir($fail_file_path, 0777, true);
        }

        //当前登录用户所属资产组允许授权数量
        $ServerAuth = new ServerAuthService();
        $remianingNum = $ServerAuth->remainingNum('client');

        if($lines > $remianingNum){
            throw new CustomException(AuthError::ADDED_HOST_MORE_THAN_ALLOW_AUTHORIZED[1], AuthError::ADDED_HOST_MORE_THAN_ALLOW_AUTHORIZED[0]);
        }

        //建库
        //查询主机最大的id
        $config = config('database.');
        $prefix = $config['prefix'];
        $hostName = $prefix.'host';
        $sql = "show table status where `name`= '{$hostName}';";
        $idArr = Db::query($sql);
        $hostIdMax = $idArr[0]['Auto_increment'];

        $hostAddMaxId = $hostIdMax + $lines;
        $hostLearnDataModel = new HostLearnDataModel();
        $hostAccessLogModel = new HostAccessLogModel();

        $databaseFinalReturn = [];

        if($hostAddMaxId > 2000){
            //赋初始值
            $index = 2001;
            if($hostIdMax > 2001){
                $index = $hostIdMax;
            }
            for($j = $index; $j <= $hostAddMaxId;){
                //创建学习数据库
                $databaseReturn1 = $hostLearnDataModel->createChildDatabaseByRoot($j);

                if(!$databaseReturn1['result']){

                    $this->setError(422, $databaseReturn1['msg']);
                    return false;
                }

                if($databaseReturn1['isNeedCreate']){
                    $tmp = [
                        //数据库名称
                        'database' => $databaseReturn1['db']['name'],
                        //成功与否
                        'result' => $databaseReturn1['result'],
                        //错误信息
                        'errorMsg' => $databaseReturn1['result'] ?  '子数据库创建成功' : $databaseReturn1['msg']
                    ];//...其他库

                    array_push($databaseFinalReturn, $tmp);
                }

                //创建防火墙日志数据库
                $databaseReturn2 = $hostAccessLogModel->createChildDatabaseByRoot($j);

                if(!$databaseReturn2['result']){
                    $this->setError(422, $databaseReturn2['msg']);
                    return false;
                }

                if($databaseReturn2['isNeedCreate']){
                    $temp = [
                        //数据库名称
                        'database' => $databaseReturn2['db']['name'],
                        //成功与否
                        'result' => $databaseReturn2['result'],
                        //错误信息
                        'errorMsg' => $databaseReturn2['result'] ?  '子数据库创建成功' : $databaseReturn2['msg']
                    ];//...其他库

                    array_push($databaseFinalReturn, $temp);
                }

                $j = $j + 2000;
            }
        }

        $fail_file_path .= md5(microtime(true)). '.html';
        $startTime = time();
        file_put_contents($fail_file_path, '<!DOCTYPE html>
        <html>
        <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        </head>
        <body>');

        if($remianingNum > 0){
            for($row = 2; $row <= 2 + $remianingNum; $row++)
            {
                $input['name'] = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
                $input['ip'] = $worksheet->getCellByColumnAndRow(2, $row)->getValue();

                $validate = Validate::make([
                    'ip|主机IP地址' => 'require|ip',
                    'name|资产名称' => 'require|length:1,50'
                ]);
                $msg = "表格第 $row 行 入库";
                
                if (!$validate->check($input))
                {
                    file_put_contents($fail_file_path, '<br>' . $msg . "未导入 错误原因 : " . $validate->getError(), FILE_APPEND);
                    continue;
                }
                //写入数据
                $res = $this->initHost($input);
                if(!$res['result'])
                {
                    file_put_contents($fail_file_path, '<br>' . $msg . " 未导入 错误原因 : {$res['msg']}", FILE_APPEND);
                    continue;
                }
                file_put_contents($fail_file_path, '<br>' . $msg . " 已导入 ", FILE_APPEND);
                $i++;
            }
        }

        $running_time = time() - $startTime;
        file_put_contents($fail_file_path, '<br>' . "应导入 $lines 条数据; 授权允许导入主机数 $remianingNum 台; 实际导入 $i 条数据; 耗时: $running_time s 
        </body>
        </html>", FILE_APPEND);
        
        $return = [
            'total' =>$lines,
            'success' => $i,
            'fail' => $lines - $i,
            'running_time' => $running_time,
            'error_file_url' => request()->domain(). '/' . $fail_file_path,
            'create_database_result' => $databaseFinalReturn
        ];

        return $return;
    }

    /**
     * @name:  初始化主机信息
     *
     * @return array
     */
    private function initHost ($input)
    {
        $learnDataModel = new HostLearnDataModel();
        $hostAccessLogModel = new HostAccessLogModel();

        $groupId = $this->getLoginHostGroupId();

        //事务操作
        Db::startTrans();
        try{
            $Host = new Host();
            $errorReturn = [
                'result' => false,
                'msg' => '数据写入失败'
            ];
            //写入主机表host
            $host_id = Db::name('host')->insertGetId(array_merge($input,[
                'group_id' => $groupId,
                'ctime' => time(),
                'tag'=>10007,
                'hostkey' => $Host->createHostKey()
                ]));
            if(!$host_id){
                Db::rollback();
                return $errorReturn;
            }

            //host_contact
            if (!Db::name('host_contact')->insert(['host_id' => $host_id, 'ip'=> $input['ip']])) {
                Db::rollback();
                return $errorReturn;
            }

            //host_hardware
            if (!Db::name('host_hardware')->insert(['host_id' => $host_id,'info'=>''])) {
                Db::rollback();
                return $errorReturn;
            }

            //host_link
            if (!Db::name('host_link')->insert([
                'host_id' => $host_id,
                'module' => 'client',
                'ip' => 0,
                'port' => 0,
                'status' => 0,
            ])
            ) {
                Db::rollback();
                return $errorReturn;
            }

            //host_module_relation
            if (!Db::name('host_module_relation')->insert(['host_id' => $host_id])) {
                Db::rollback();
                return $errorReturn;
            }

            //创建学习数据表
            if (!$learnDataModel->createTable($host_id)) {
                Db::rollback();
                return $errorReturn;
            }

            //日志表
            if (!$hostAccessLogModel->createTable($host_id)) {
                Db::rollback();
                return $errorReturn;
            }
            Db::commit();
            return [
                'result' => true,
                'msg' => '写入成功'
            ];
        }catch (Exception $e){
            Db::rollback();
            return $errorReturn;
        }

    }
    /**
     * @name: 
     * @Param: 
     * @Description: 
     * @Author: foo
     * @Date: 2020-07-28 12:54:37
     */
    private function keyValue ()
    {
        $return = [
            'name' => '资产名称',
            'ip' => '主机IP地址'
        ];

        return $return;
    }

    /**
     * @name: 表单数据
     * @Param: 
     * @Description: 
     * @Author: foo
     * @Date: 2020-07-27 16:53:19
     */
    private function sheetData (&$sheet, $title, $data)
    {
        foreach ($title as $key => $value) {
            // 单元格内容写入
            $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
        }
        $row = 2; // 从第二行开始
        foreach ($data as $item) {
            $column = 1;
            foreach ($item as $value) {
                // 单元格内容写入
                $sheet->setCellValueByColumnAndRow($column, $row, $value);
                $column++;
            }
            $row++;
        }
    }
}